﻿using System;
using System.Collections.Generic;
using System.Text;
using ADOX;
using System.Data.OleDb;


namespace TcpServer
{
    class databaseManagement
    {
        private string databaseName = "DB_transfer";
        private string databasePath = ".";
        private int lastId;


        public void setDatabasePathAndDatabaseName(string databasePath, string databaseName)
        {
            this.databasePath = databasePath;
            this.databaseName = databaseName;
        }

        private string connectString(){
            return ("Provider=Microsoft.Jet.OLEDB.4.0"
               + ";Data Source=" + this.databasePath + "//" + this.databaseName + ".mdb;");   
        }

        public void createDatabase(string databaseName)
        {
            this.createDatabase(".", databaseName);
        }

        [STAThread]
        public void insertNewData(string fileName , int fileSize)
        {
            try
            {
                this.insetData(fileName, fileSize);
            }
            catch (Exception ex)
            {
                this.createDatabase();
                this.insetData(fileName, fileSize);
            }
        }

        public void createDatabase()
        {
            this.createDatabase(this.databasePath , this.databaseName );
        }

        [STAThread]
        public void createDatabase(string databasePath ,string databaseName)
        {
            this.setDatabasePathAndDatabaseName(databasePath , databaseName);
            

            ADOX.CatalogClass cat = new ADOX.CatalogClass();

            string connectString = this.connectString();

            cat.Create(connectString + "Jet OLEDB:Engine Type=5");

            cat = null;
            
            string createTableSql = "create table transferLog("
               + "LogId AUTOINCREMENT,"
               + "FileName VarChar(30),"
               + "FileSize Number,"
               + "ReceiveSize Number,"
               + "StartDate Date,"
               + "FinishDate Date,"
               + "Status VarChar(10))";
            OleDbConnection conn = new OleDbConnection(connectString);
            OleDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = createTableSql;
            try
            {
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                cmd.Connection.Close();
            }
        }

        [STAThread]
        public void insetData(string FileName,int FileSize)
        {
            string connectString = this.connectString();
            using (OleDbConnection cn = new OleDbConnection(connectString))
            {
                string sql = "INSERT INTO transferLog (FileName,FileSize,StartDate,Status) "
                    + "VALUES ("
                            + "'"+ FileName + "'"
                            + ","+ FileSize
                            + ","
                            + DateTime.Now.AddHours(0).ToOADate()
                            + ", 'Create')";
                string sql2 = "select @@Identity";
                OleDbCommand cmd = new OleDbCommand(sql, cn);
                
                try
                {
                    cn.Open();
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = sql2;
                    this.lastId = (int)cmd.ExecuteScalar();
                }
                finally
                {
                    cmd.Connection.Close();
                }
                
            }
        }


        [STAThread]
        public void updateStatus(string status)
        {
            using (OleDbConnection cn = new OleDbConnection(this.connectString()))
            {
                string sql = "UPDATE transferLog SET Status = '"+ status +"' WHERE LogId = "+ this.lastId ;
                
                OleDbCommand cmd = new OleDbCommand(sql, cn);

                try
                {
                    cn.Open();
                    cmd.ExecuteNonQuery();
                }
                finally
                {
                    cmd.Connection.Close();
                }

            }
        }

        [STAThread]
        public void updateFinish(long ReceiveSize)
        {
            using (OleDbConnection cn = new OleDbConnection(this.connectString()))
            {
                string sql = "UPDATE transferLog SET "
                            + "Status = 'Finish',"
                            +"ReceiveSize = " + ReceiveSize +","
                            + "FinishDate = " + DateTime.Now.AddHours(0).ToOADate()
                            +" WHERE LogId = " + this.lastId;

                OleDbCommand cmd = new OleDbCommand(sql, cn);

                try
                {
                    cn.Open();
                    cmd.ExecuteNonQuery();
                }
                finally
                {

                    cmd.Connection.Close();
                    
                }

            }
        }

    }
}
